package com.eascs.afterloan.service;

import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.eascs.admin.api.form.O2oAfterLoanForm;
import com.eascs.admin.common.service.BaseSevice;
import com.eascs.admin.o2o.loan.dao.O2oLoanapplyDao;
import com.eascs.admin.o2o.loan.entity.O2oLoanapplyEntity;
import com.eascs.afterloan.dao.BankCardDao;
import com.eascs.afterloan.entity.O2oBankCard;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.common.util.DateUtils;
import com.eascs.web.sso.User;
import com.eascs.web.sso.UserHolder;

import jodd.util.StringUtil;

@Service
public class BankCardService extends BaseSevice {
	private final Logger logger = LoggerFactory.getLogger(BankCardService.class);

	@Autowired
	BankCardDao bankCardDao;
	@Autowired
	private O2oLoanapplyDao loanDao;

	public PageResult<Object[]> findByPageList(O2oAfterLoanForm o2oAfterLoanForm) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, o2oAfterLoanForm.getPageSize(),
				o2oAfterLoanForm.getO2oIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append(
				" select oll.bill_code,opr.province_name,olu.user_full_name,CONCAT(LEFT (olu.document_no, 3),'****',RIGHT (olu.document_no, 3)) as document_no,");
		querySql.append("  oll.bank_loan_name,olu.user_full_name as bank_account_name, bank_account,receive_bank");
		querySql.append(
				" from  o2o_loanapply oll LEFT JOIN o2o_loan_user olu on oll.loanapply_no = olu.loanapply_no and oll.loan_id = olu.loan_id ");
		querySql.append(" LEFT JOIN o2o_platform_regional opr on oll.ltd = opr.platform_id ");
		querySql.append(
				" where /**DATA_PERMISSION(oll.ltd)**/  oll.bank_loan_name='JCB' and oll.wf_type in ('NF','S','O','SN','HN','T','DC','YQ')  and olu.sponsor_type is null and olu.spouse_id is null ");
		if (!StringUtil.isEmpty(o2oAfterLoanForm.getBillCode())) {
			querySql.append(" AND oll.bill_code = ? ");
			pageQuery.addParam(o2oAfterLoanForm.getBillCode());
			logger.debug("params bill_code={}", o2oAfterLoanForm.getBillCode());
		}
		if (!StringUtil.isEmpty(o2oAfterLoanForm.getLoanUserName())) {
			querySql.append(" AND olu.user_full_name like ? ");
			pageQuery.addLikeParam(o2oAfterLoanForm.getLoanUserName());

		}
		if (!StringUtil.isEmpty(o2oAfterLoanForm.getHostProvince())) {
			querySql.append(" AND  opr.province_code  = ? ");
			pageQuery.addParam(o2oAfterLoanForm.getHostProvince());
		}
		if (!StringUtil.isEmpty(o2oAfterLoanForm.getCreateDateStart())) {
			querySql.append(" AND  oll.create_date >= ?");
			pageQuery.addParam(o2oAfterLoanForm.getCreateDateStart());
		}
		if (!StringUtil.isEmpty(o2oAfterLoanForm.getCreateDateEnd())) {
			querySql.append(" AND oll.create_date < ?");
			pageQuery.addParam(DateUtils.formateStandDate(
					DateUtils.addDate(DateUtils.formateStandDate(o2oAfterLoanForm.getCreateDateEnd()), 1)));
		}

		querySql.append(" ORDER BY bank_account asc,oll.create_date DESC ");
		pageQuery.setQuerySql(querySql.toString());
		return bankCardDao.findDataWithPage(pageQuery);
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public String addList(List<List<Object>> results) {
		User user = UserHolder.getUser();
		loanDao.execute("update o2o_bank_card set flag='0'");//把原来导入的数据标记下
		for (int i = 0; i < results.size(); i++) {
			List<Object> list = (List) results.get(i);
			String loanerName = list.get(0).toString().trim(); // 借款人姓名
			String idNum = list.get(1).toString().trim(); // 身份证号码
			String bankCardNum = list.get(2).toString().trim(); // 银行卡号
			String receiveBank = list.get(3).toString().trim(); // 开户行全称
			O2oBankCard bank = new O2oBankCard();
			bank.setLoanerName(loanerName);
			bank.setIdNum(idNum);
			bank.setBankCardNum(bankCardNum);
			bank.setReceiveBank(receiveBank);
			bank.setCreator(user.getCname());
			bank.setCreateTime(new Date());
			bank.setFlag("1");
			List<O2oLoanapplyEntity> O2oLoanapplyList = loanDao.findJxbLoanByIdcardNum(idNum);
			if (O2oLoanapplyList.isEmpty() || O2oLoanapplyList.size() == 0) {

				bank.setMessage("未导入");
			} else {
				String update_sql = "update o2o_loanapply set bank_account=?,receive_bank=? where bill_code=?";
				for (O2oLoanapplyEntity entity : O2oLoanapplyList) {
					loanDao.execute(update_sql, bankCardNum, receiveBank, entity.getBillCode());
					logger.info("params bill_code={}", entity.getBillCode());
				}
				bank.setMessage("已导入更新");
			}
			bankCardDao.insert(bank);
		}
		return "success";
	}
	
	public PageResult<Object[]> findBankCardByPageList(O2oAfterLoanForm o2oAfterLoanForm) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, o2oAfterLoanForm.getPageSize(),
				o2oAfterLoanForm.getO2oIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append("select loaner_name,id_num,bank_card_num,receive_bank,message from o2o_bank_card where flag='1' ");
		querySql.append(" ORDER BY create_time DESC ");
		pageQuery.setQuerySql(querySql.toString());
		return bankCardDao.findDataWithPage(pageQuery);
	}
	
	/**
	 * 根据贷款单号查询放款期限
	 * @param billCode
	 * @return
	 */
	public String getBankTerm(String billCode){
		String sql = "select loan_term from o2o_bank_loan_data where bill_code=?";
		return bankCardDao.findString(sql, billCode);
	}
}
